package esms.etonenet.boss1069.repository;

import java.util.List;

import org.springframework.data.jpa.repository.Query;

import esms.etonenet.boss1069.entity.SpChannel;
import esms.etonenet.boss1069.entity.SpChannelPK;
import esms.etonenet.common.repository.BaseRepository;

public interface SpChannelRepository extends BaseRepository<SpChannel, SpChannelPK> {

	@Query(nativeQuery = true, value = "update ts_user_group set logic_stat = 1 where user_group_id = ?1")
	void delUserGroup(Long ugid);

	@Query(nativeQuery = true, value = "select DISTINCT sp.sp_id||'-'||sp.sp_name label,sp.sp_id||'-'||sp.sp_name value from TM_SP_CHANNEL spch, TM_SP sp where (sp.sp_id like %?1% or lower(sp.sp_name) like '%'||lower(?1)||'%') and SPCH.sp_id = sp.sp_id and ROWNUM <= 20")
	List<Object[]> autoSp(String term);

	@Query(nativeQuery = true, value = "select DISTINCT CH.CHANNEL_ID||'-'||CH.CHANNEL_NAME label,CH.CHANNEL_ID||'-'||CH.CHANNEL_NAME value from TM_SP_CHANNEL spch, TM_CHANNEL ch where spch.sp_id = ?1 and (ch.channel_id like %?2% or lower(ch.channel_name) like '%'||lower(?2)||'%') and SPCH.CHANNEL_ID = CH.CHANNEL_ID and ROWNUM <= 20")
	List<Object[]> autoChannel(String spId, String term);

	@Query(nativeQuery = true, value = "select DISTINCT sp_service_code label,sp_service_code value from TM_SP_CHANNEL where sp_id = ?1 and channel_id = ?2 and sp_service_code like %?3% and ROWNUM <= 20")
	List<Object[]> autoSpServiceCode(String spId, String channelId, String term);
}
